#! /usr/bin/env python
#coding=utf-8
from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import time, sys
sys.path.insert(0, '../apps')

from departments.models import *
from myuser.models import *

from systems.models import *
from requirements.models import *
from assignments.models import *

from charts.models import *
from tasks.models import *

#from departments.models import *
#from requirements.models import *
#from charts.models import *
#from charts.models import *
import datetime
from datetime import date
from get_month_quarter import *
from get_tasks_develop_report_detail import get_task_plan_delay
#from __future__ import division

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()

#meta = MetaData(db)
#db.metadata.reflect()

def get_manage_requirement(month_first_day,month_last_day,report_type):
    
    #从settings.ini获取任务类型编号
    settings = Ini('../apps/settings.ini')

    #assignment_type = dict(settings.PARA.ASSIGNMENT_TYPE)
    req_req = get_model('requirement')
    req_dev = get_model('tasks_develop')
    req_res = get_model('researches')
    req_ndev = get_model('tasks')
    req_detal = get_model('tasks_details')
#    req_plan = get_model('milestone_plan')
#    tasks_milestone = get_model('tasks_milestone')
    req_sys = get_model('system')
    #获取本周末日期

    #函数datediff计算两个日期之间的天数
#    def datediff(beginDate,endDate):
#        #format="%Y-%m-%d";
#        #bd=strtodatetime(beginDate,format)
#        #ed=strtodatetime(endDate,format)    
#        bd = beginDate
#        ed = endDate        
#        oneday=datetime.timedelta(days=1)
#        count=0
#        #print ed
#        while bd!=ed:
#            ed=ed-oneday
#            count+=1
#        return count
    today = datetime.date.today()


    #清理垃圾数据结束
    
    #first_day,last_day = get_week()
    last_day = month_last_day
    first_day = month_first_day
#    print last_day,first_day

    id_tmp = select([req_req.c.id,req_req.c.title,req_req.c.tx,req_sys.c.kfzx,req_req.c.mode],    
                     and_(~req_req.c.id.in_(select([req_req.c.id],
                                            and_(req_req.c.modified_date < first_day,
                                            req_req.c.stage.in_(['FN'])))),
                            req_req.c.stage != 'CL',
                            req_req.c.stage != '19',             
                            req_sys.c.id == req_req.c.main_sys))
    #print 'id_tmp', id_tmp
    id_tmp_rs = id_tmp.execute()
    #data_tmp = {}
    data={}
    for row in id_tmp_rs:
        id,title,tx,kfzx,mode = row
        
#        print 'a',id,tx,kfzx,mode
        #data_tmp['req_id'] = a
        #初始化数据开始
        y = str(id)
        data[y] = {}
        data[y]['req_id'] = id
        data[y]['plan_stat'] = 0
        data[y]['inner_resource_input'] = 0
        data[y]['outer_resource_input'] = 0
        data[y]['delay_stat'] = ''
        data[y]['delay_reason'] = ''
        data[y]['finish_stat'] = 0
        data[y]['task_amount'] = 0
        data[y]['new_task_amount'] = 0
        data[y]['report_date'] = last_day
        data[y]['report_type'] = report_type
        data[y]['delay_flag'] = 0
        data[y]['version_amount'] = 0
        data[y]['inner_resource_num'] = 0
        data[y]['outer_resource_num'] = 0
        data[y]['req_name'] = title
        data[y]['kfzx'] = kfzx
        data[y]['tx'] = tx
        if mode == '01':#准开发需求以项目方式实施
            data[y]['t_project'] = 1
        else:
            data[y]['t_project'] = 0
        if mode == '02':#准开发需求以非项目方式实施
            data[y]['t_nproject'] = 1
        else:
            data[y]['t_nproject'] = 0
        if mode == '03':#准开发需求以无任务书开发方式实施
            data[y]['t_nassignment'] = 1
        else:
            data[y]['t_nassignment'] = 0
        if mode == '09':#准开发需求以待定方式实施
            data[y]['t_undetermined'] = 1
        else:
            data[y]['t_undetermined'] = 0
        data[y]['t_delay_flag'] = 0
     
    #过滤准开发需求阶段为可研阶段且没有开发任务的准开发需求
    
    for data2 in data.items():
        x,y =data2
        x = int(x)
        task_num = select([func.count(func.distinct(req_dev.c.id)),req_dev.c.requirement],
                            and_(req_dev.c.requirement == x,
#                                 ~req_dev.c.stage.in_(['00']),
                                 or_(~req_dev.c.stage.in_(['00']),
                                    and_(req_dev.c.stage == '00',
                                         req_dev.c.begin_time <= last_day)),
                                 req_req.c.id == x,
                                 ~req_req.c.stage.in_(['18','19','1A','26','31','FN']))
                            ).group_by(req_dev.c.requirement)
        
        task_num_rs = task_num.execute()
        for row in task_num_rs:
            num_line,b = row
            x = str(x)
            if num_line==0:
                data.pop(x)
                #print 'x',x
    ##   
        
        #初始化数据结束
        #print data_tmp
        #r = req_model_tmp(**data_tmp)
        #r.save()
    #print 'data',data
    #test = list(data)
#    for a in test:
#        #a=list(a)
#        print 'a',a
        
    for data1 in data.items():
        x,y =data1
        #x = 67
        #print 'data1',x
        x = int(x)
        id_tmp = str(x)
        #计算资源投入比例
        tj_dev = select([req_dev.c.requirement,\
                  req_dev.c.amount_inner,\
                  req_dev.c.amount_outer,\
                  req_dev.c.amount_inner_input,\
                  req_dev.c.amount_outer_input,\
#                  req_dev.c.id,\
#                  req_dev.c.begin_time,\
#                  req_dev.c.end_time
                  ],and_(req_dev.c.requirement == x)
                        )#.group_by(req_dev.c.id)
        tj_ndev = select([req_ndev.c.requirement,\
                  req_ndev.c.amount_inner,\
                  req_ndev.c.amount_outer,\
                  req_ndev.c.amount_inner_input,\
                  req_ndev.c.amount_outer_input,\
#                  req_ndev.c.id,\
#                  req_ndev.c.begin_time,\
#                  req_ndev.c.end_time                  
                  ],and_(req_ndev.c.requirement == x
                        )
                        )#.group_by(req_ndev.c.id)
                
        tj_res = select([req_res.c.requirement,\
                  req_res.c.amount_inner,\
                  req_res.c.amount_outer,\
                  req_res.c.amount_inner_input,\
                  req_res.c.amount_outer_input,\
#                  req_res.c.id,\
#                  req_res.c.begin_time,\
#                  req_res.c.end_time                  
                  ],and_(req_res.c.requirement == x
                        )
                        )#.group_by(req_res.c.id)
        
        amount_inner = 0
        amount_outer = 0
        amount_inner_input = 0
        amount_outer_input = 0
        inner_resource_num = 0
        outer_resource_num = 0
        inner_resource_input = 0.0
        outer_resource_input = 0.0
#        inner_avg = 0
#        outer_avg = 0
        tj_dev_rs = tj_dev.execute()

        for row in tj_dev_rs:
            id,inner_plan,outer_plan,inner_input,outer_input = row
#            print 'dev:id,inner_plan,outer_plan,inner_input,outer_input',id,inner_plan,outer_plan,inner_input,outer_input
            #计算从任务开始时间到任务结束时间的天数
            inner_resource_num += inner_input
            outer_resource_num += outer_input
            
            if outer_plan > 0 and outer_input > 0:
#                print 'outer_plan'                
                amount_outer += outer_plan
                amount_outer_input += outer_input
#                outer_resource_input += float(outer_input)/outer_plan
#                outer_avg += 1
                
            if inner_plan > 0 and inner_input > 0:
#                print 'inner_plan'
                amount_inner += inner_plan
                amount_inner_input += inner_input
#                inner_resource_input += float(inner_input)/inner_plan
#                inner_avg += 1
                
        #print 'amount_inner',amount_inner,
        #print 'amount_inner_input',amount_inner_input
        tj_ndev_rs = tj_ndev.execute()
        for row in tj_ndev_rs:
            id,inner_plan,outer_plan,inner_input,outer_input = row
#            print 'ndev:id,inner_plan,outer_plan,inner_input,outer_input',id,inner_plan,outer_plan,inner_input,outer_input            
            inner_resource_num += inner_input
            outer_resource_num += outer_input
            
            if outer_plan > 0 and outer_input > 0:                
                amount_outer += outer_plan
                amount_outer_input += outer_input
#                outer_resource_input += float(outer_input)/outer_plan
#                outer_avg += 1
            if inner_plan > 0 and inner_input > 0:
                amount_inner += inner_plan
                amount_inner_input += inner_input
#                inner_resource_input += float(inner_input)/inner_plan
#                inner_avg += 1
            
        #print 'amount_inner',amount_inner,
        #print 'amount_inner_input',amount_inner_input    
        tj_res_rs = tj_res.execute()
        for row in tj_res_rs:
            id,inner_plan,outer_plan,inner_input,outer_input = row
#            print 'res:id,inner_plan,outer_plan,inner_input,outer_input',id,inner_plan,outer_plan,inner_input,outer_input            
            inner_resource_num += inner_input
            outer_resource_num += outer_input
            
            if outer_plan > 0 and outer_input > 0:                
                amount_outer += outer_plan
                amount_outer_input += outer_input
#                outer_resource_input += float(outer_input)/outer_plan
#                outer_avg += 1
                
            if inner_plan > 0 and inner_input > 0:
                amount_inner += inner_plan
                amount_inner_input += inner_input
#                inner_resource_input += float(inner_input)/inner_plan
#                inner_avg += 1
                
#        print 'x,amount_inner,amount_outer,amount_inner_input,amount_outer_input',x,amount_inner,amount_outer,amount_inner_input,amount_outer_input   
        #print 'amount_inner',amount_inner,
        #print 'amount_inner_input',amount_inner_input
        if amount_inner_input==0 and amount_inner==0:
            inner_resource_input = 0
        elif amount_inner_input!=0 and amount_inner==0:
            inner_resource_input = amount_inner_input
        else:
            inner_resource_input = float(amount_inner_input)/amount_inner
        if amount_outer_input==0 and amount_outer==0:
            outer_resource_input = 0
        elif amount_outer_input!=0 and amount_outer==0:
            outer_resource_input = amount_outer_input
        else:
            outer_resource_input = float(amount_outer_input)/amount_outer
        #print inner_resource_input,outer_resource_input
        data[id_tmp]['inner_resource_num'] = float(inner_resource_num)/22
        data[id_tmp]['outer_resource_num'] = float(outer_resource_num)/22
        data[id_tmp]['inner_resource_input'] = inner_resource_input
        data[id_tmp]['outer_resource_input'] = outer_resource_input
#        if inner_avg > 0:
#            data[id_tmp]['inner_resource_input'] = inner_resource_input/inner_avg
#        else:
#            data[id_tmp]['inner_resource_input'] = inner_resource_input
#        
#        if outer_avg > 0:
#            data[id_tmp]['outer_resource_input'] = outer_resource_input/outer_avg
#        else:
#            data[id_tmp]['outer_resource_input'] = outer_resource_input
        
        #计算计划计划执行比例(按照当前时间减第一个任务的开始时间减做分子，最后一个任务的结束时间减第一个任务的开始时间做分母)
        task_begin = select([func.min(req_dev.c.begin_time)],
                       and_(req_dev.c.requirement == x),
                            ).group_by(req_dev.c.requirement)
        task_end = select([func.max(req_dev.c.end_time)],
                       and_(req_dev.c.requirement == x),
                            ).group_by(req_dev.c.requirement)
        
#        plan_finish = select([func.count(func.distinct(req_plan.c.id))],
#                            or_(and_(req_dev.c.requirement == x,
#                                 req_plan.c.task == req_dev.c.id,
#                                 req_plan.c.status == '3'),
##                            and_(req_ndev.c.requirement == x,
##                                 req_plan.c.task == req_ndev.c.id,
##                                 req_plan.c.status == '3'),
##                            and_(req_res.c.requirement == x,
##                                 req_plan.c.task == req_res.c.id,
##                                 req_plan.c.status == '3')
#                            )).group_by(req_dev.c.requirement)
#                            #.group_by(req_dev.c.requirement,req_ndev.c.requirement,req_res.c.requirement)
#        #print 'plan',plan
#        #print 'plan_finish',plan_finish
        task_begin_rs = task_begin.execute()
        task_end_rs = task_end.execute()
        oneday = datetime.timedelta(days=1)
        day_init = datetime.date(*time.strptime('1900-01-01', '%Y-%m-%d')[:3])
        task_begin = day_init
        task_end = day_init
        #q=0
        plan_stat = 0
        for row in task_begin_rs:
            p, = row
            task_begin = p
        for row in task_end_rs:
            q, = row
            task_end = q
        if task_begin == day_init or task_end == day_init or task_begin == task_end:
            plan_stat = 0
        elif task_end < last_day:
            plan_stat = 1
        elif task_begin > last_day:
            plan_stat = 0
        else:
            plan_stat = float(datediff(task_begin,last_day))/datediff(task_begin,task_end)
        
        data[id_tmp]['plan_stat'] = plan_stat
#        print 'task_begin,task_end',x,task_begin,task_end
#        print 'last_day - task_begin',last_day - task_begin
#        print 'task_end-last_day.day',task_end-last_day
#        if task_end >= last_day:
#           print 'datediff',datediff(last_day,task_end)
#        print 'plan_stat',x,plan_stat
#        if x==67:
#        print 'x,p',x,p
#        print 'x,q',x,q
#        print 'x,plan_stat',x,plan_stat

        #判断准开发需求下任务是否有计划延迟
        weight_delay = 0 
        weight_total = 0
        delay_stat = ''
        delay_stat_t = ''
        #获取任务里程碑或计划延迟情况
        dev_task_delay = req_dev.filter(req_dev.c.requirement == x)
        for row in dev_task_delay:
            #暂停和异常终止的任务不需要判断是否延迟，20111110修改
            if row.status != '4' and row.status != '5':
                delay_stat_t,weight_delay,weight_total = get_task_plan_delay(row,first_day,last_day)
                delay_stat = delay_stat + delay_stat_t
        

        data[id_tmp]['delay_stat'] = delay_stat                 
        #-------------------------------------

#        #20110128新增是否延迟标志字段

        
        #初始化延迟原因
        delay_reason = ''
        
        if delay_stat:
            data[id_tmp]['delay_flag'] = 1
            data[id_tmp]['t_delay_flag'] = 1
            #取延迟原因说明开始
            reason = select([req_dev.c.task_id,req_dev.c.delay_reason],
                            and_(req_dev.c.requirement == x,
                                 #req_dev.c.task_id == dev_task
                                ))
            reason_rs = reason.execute()
            
            for row in reason_rs:
                dev_task,delay_rea = row
                #print 'delay_rea',delay_rea
                #print 'req_id,delay_rea',req_id,delay_rea
                if delay_rea:
                    #print 'req_id,delay_rea',req_id#,delay_rea
                    delay_reason = delay_reason + str(dev_task) + '-' + delay_rea + '\n'
            
            #取延迟原因说明结束
        #将组合好的延迟原因保存到data
        data[id_tmp]['delay_reason'] = delay_reason
            

        #取完成状态
        finish_stat = 0
        req_stage = ''
        finish_flag = select([func.count(func.distinct(req_dev.c.id))],
                            and_(req_dev.c.requirement == x,
                                req_dev.c.stage != 'FN'),
                            )
        req_flag = select([req_req.c.stage],
                           and_(req_req.c.id == x))
        
        req_flag_rs = req_flag.execute()                
        finish_flag_rs = finish_flag.execute()
        for row in finish_flag_rs:
            a, = row
            finish_stat = a
        for row in req_flag_rs:
            req_stage, = row
        #取非开发任务完成情况

        ndev_finish_flag = select([func.count(func.distinct(req_ndev.c.id))],
                                and_(req_ndev.c.requirement == x,
                                req_ndev.c.status != '3'),
                                )

        ndev_finish_flag_rs = ndev_finish_flag.execute()
        for row in ndev_finish_flag_rs:
            a, = row
            finish_stat += a
        
        #取可研任务完成情况
        res_finish_flag = select([func.count(func.distinct(req_res.c.id))],
                                  and_(req_res.c.requirement == x,
                                  req_res.c.status != '3'),
                                )

        res_finish_flag_rs = res_finish_flag.execute()
        for row in res_finish_flag_rs:
            a, = row
            finish_stat += a

        if finish_stat == 0 and req_stage == 'FN':
            data[id_tmp]['finish_stat'] = 1
        else:
            data[id_tmp]['finish_stat'] = 0
        #print 'x,finish_stat,req_stage',x,finish_stat,req_stage,data[id_tmp]['finish_stat']
        #取该准开发需求下投产版本数量
        v_amount = select([func.count(func.distinct(req_dev.c.version_date))],
                       and_(req_dev.c.requirement == x),
                            ).group_by(req_dev.c.requirement)
        v_amount_rs = v_amount.execute()
        version_amount = 0
        for row in v_amount_rs:
            dev_amount, = row
            version_amount = dev_amount    
             
        data[id_tmp]['version_amount'] = version_amount
        #print 'version_amount',id_tmp,version_amount
        
        #取该准开发需求下任务数
        amount = select([func.count(func.distinct(req_dev.c.id))],
                       and_(req_dev.c.requirement == x),
                            )
        amount_rs = amount.execute()
        task_amount = 0
        for row in amount_rs:
            dev_amount, = row
            task_amount = dev_amount    
        
        amount_ndev = select([func.count(func.distinct(req_ndev.c.id))],
                          and_(req_ndev.c.requirement == x),
                            )
        amount_ndev_rs = amount_ndev.execute()
        for row in amount_ndev_rs:
            ndev_amount, = row
            task_amount += ndev_amount 

        amount_res = select([func.count(func.distinct(req_res.c.id))],
                           and_(req_res.c.requirement == x),
                            )
        amount_res_rs = amount_res.execute()
        for row in amount_res_rs:
            res_amount, = row
            task_amount += res_amount 

        
        data[id_tmp]['task_amount'] = task_amount
        #计算本周期内新增任务数
        

        new_amount = select([func.count(func.distinct(req_dev.c.id))],
                          and_(req_dev.c.requirement == x,
                                req_dev.c.created_date >= first_day,
                                req_dev.c.created_date <= last_day),
                            )
        
        new_amount_rs = new_amount.execute()
        new_task_amount = 0
        for row in new_amount_rs:
            dev_amount, = row       
#            print dev_amount
            new_task_amount += dev_amount
        new_amount_ndev = select([func.count(func.distinct(req_ndev.c.id))],
                          and_(req_ndev.c.requirement == x,
                                req_ndev.c.created_date >= first_day,
                                req_ndev.c.created_date <= last_day),
                            )
        new_amount_ndev_rs = new_amount_ndev.execute()

        for row in new_amount_ndev_rs:
            ndev_amount, = row
#            print dev_amount
            new_task_amount += ndev_amount
            
        new_amount_res = select([func.count(func.distinct(req_res.c.id))],
                          and_(req_res.c.requirement == x,
                                req_res.c.created_date >= first_day,
                                req_res.c.created_date <= last_day),
                            )
        new_amount_res_rs = new_amount_res.execute()
        
        for row in new_amount_res_rs:
            res_amount, = row
#            print dev_amount
            new_task_amount += res_amount
                
        data[id_tmp]['new_task_amount'] = new_task_amount
        #break
    return data


